﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Text;
using UniRlv.COL;
using UniRlv.Server;

namespace UniRlv.Utility
{
    public static class DALOpr
    {
        public static bool TestConnect(ref string msg)
        {
            //return SqlHelper.TestConnect(ref msg);        
                
            try
            {
                string write = "select count(id) as num from [User]";
                object scalar = SqlHelper.ExecuteScalar(write, ref msg);
                if (scalar != null)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                msg = ex.Message;
                return false;
            }
        }

        #region 通用添加/更新操作

        public static bool ExecuteNonQuery(string sql, ref string errDesc)
        {
            Debug.WriteLine("ExecuteNonQuery: " + sql);
            return SqlHelper.ExecuteNonQuery(sql, ref errDesc)>=0;
        }

        public static bool ExecuteNonQueryTran(string sql, ref string errDesc)
        {
            Debug.WriteLine("ExecuteNonQueryTran: " + sql);

            string[] sqls = sql.Split(new char[]
            {
                '|'
            });

            errDesc = sql;

            return SqlHelper.ExecuteSqlTran(sqls,  ref errDesc);
        }

        public static bool ExecuteSqlTran(string[] sqls, ref string errDesc)
        {
            return SqlHelper.ExecuteSqlTran(sqls, ref errDesc);
        }

        public static bool Insert<T>(T t, ref string errDesc) where T : class, new()
        {
            string sql = EntityHelper.GetInsertSql(t);
            Debug.WriteLine("Insert: " + sql);
            return SqlHelper.ExecuteNonQuery(sql,  ref errDesc) > 0;
        }

        public static bool Update(Dictionary<string, object> dataDic, string whereString, string tbName, ref string errDesc)
        {
            string sql = EntityHelper.GetUpdateSql(dataDic, whereString, tbName);
            return SqlHelper.ExecuteNonQuery(sql,  ref errDesc) > 0;
        }

        #endregion


        #region 通用查询操作

        public static object RawGetScalar(string sql, ref string errDesc)
        {
            Debug.WriteLine("RawGetScalar: " + sql);
            return SqlHelper.ExecuteScalar(sql,  ref errDesc);
        }

        public static string RawQuery(string sql)
        {
            Debug.WriteLine("RawQuery: " + sql);

            List<Dictionary<string, object>> li = new List<Dictionary<string, object>>();
            Dictionary<string, object> res = new Dictionary<string, object>();
            bool bRes = false; ;
            string errDesc = string.Empty;

            DataTable dt = SqlHelper.ExecuteTable(sql, ref errDesc);

            if (dt != null)
            {
                Dictionary<string, object> dic;
                foreach (DataRow dr in dt.Rows)
                {
                    dic = new Dictionary<string, object>();
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        dic.Add(dt.Columns[i].ColumnName, dr[i].ToString());
                    }

                    li.Add(dic);
                }
                bRes = true;
            }



            res.Add("msg", errDesc);
            res.Add("res", bRes);
            res.Add("obj", JsonHelper.ToJson<List<Dictionary<string, object>>>(li));

            return JsonHelper.ToJson<Dictionary<string, object>>(res);
        }

        public static string RawQueryArchieve(string sql)
        {
            Debug.WriteLine("RawQueryArchieve: " + sql);

            List<Dictionary<string, object>> li = new List<Dictionary<string, object>>();
            Dictionary<string, object> res = new Dictionary<string, object>();
            bool bRes = false; ;
            string errDesc = string.Empty;

            DataTable dt = SqlHelper.ExecuteTable("server=.\\SQLEXPRESS08;database=UniRlv_archive;uid=sa;pwd=connortao", sql, ref errDesc);

            if (dt != null)
            {
                Dictionary<string, object> dic;
                foreach (DataRow dr in dt.Rows)
                {
                    dic = new Dictionary<string, object>();
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        dic.Add(dt.Columns[i].ColumnName, dr[i].ToString());
                    }

                    li.Add(dic);
                }
                bRes = true;
            }



            res.Add("msg", errDesc);
            res.Add("res", bRes);
            res.Add("obj", JsonHelper.ToJson<List<Dictionary<string, object>>>(li));

            return JsonHelper.ToJson<Dictionary<string, object>>(res);
        }

        public static List<T> Query<T>(string whereString, ref string msg) where T : class, new()
        {
            string sql = EntityHelper.GetQuerySql<T>();
            sql += " where " + whereString;
            string res = RawQuery(sql);

            ResultEntity re = EntityHelper.GetResultEntityFromResult(res);
            Debug.WriteLine("Query: " + sql);
            if (re.res)
            {
                return EntityHelper.GetObjectsFromResultEntity<T>(re);
            }
            else
            {
                msg = "查询任务出错：" + re.msg;
                return null;
            }

        }

        public static List<T> QueryArchieve<T>(string whereString, ref string msg) where T : class, new()
        {
            string sql = EntityHelper.GetQuerySql<T>();
            sql += " where " + whereString;
            string res = RawQueryArchieve(sql);

            ResultEntity re = EntityHelper.GetResultEntityFromResult(res);
            Debug.WriteLine("Query: " + sql);
            if (re.res)
            {
                return EntityHelper.GetObjectsFromResultEntity<T>(re);
            }
            else
            {
                msg = "查询任务出错：" + re.msg;
                return null;
            }

        }

        public static List<T> QueryAll<T>(ref string msg) where T : class, new()
        {
            return Query<T>("1=1", ref msg);
        }

        public static int QueryWholeQuantity<T>(ref string msg) where T : class, new()
        {
            return QueryLimitQuantity<T>("1=1", ref msg);
        }

        public static int QueryLimitQuantity<T>(string whereString, ref string msg) where T : class, new()
        {
            string tbName = typeof(T).Name;
            string sql = string.Format("select count(*) from {0} where {1}", tbName, whereString);
            return Convert.ToInt32(RawGetScalar(sql, ref msg));
        }

        public static List<T> QueryPage<T>(int page, int pageSize, ref string msg) where T : class, new()
        {
            return QueryPage<T>(page, pageSize, "1=1", ref msg);
        }

        public static List<T> QueryPage<T>(int page, int pageSize, string whereString, ref string msg) where T : class, new()
        {
            string tbName = typeof(T).Name;
            string sql = string.Format("SELECT w2.n, w1.* FROM {0} w1, (SELECT TOP {1} row_number() OVER (ORDER BY ID DESC) n, ID FROM {2} where {4}) w2 WHERE w1.ID = w2.ID AND w2.n > {3} ORDER BY w2.n ASC ", tbName, (page) * pageSize, tbName, (page - 1) * pageSize, whereString);

            string res = RawQuery(sql);
            ResultEntity re = EntityHelper.GetResultEntityFromResult(res);

            if (re.res)
            {
                return EntityHelper.GetObjectsFromResultEntity<T>(re);
            }
            else
            {
                msg = "查询任务出错：" + re.msg;
                return null;
            }
        }
        public static List<CodeInfo> QueryCodeHistoryPageByProduct(int page, int pageSize, int productID, ref string msg)
        {
            string sql = string.Format("SELECT b.rowNum, a.* FROM {0} a, (SELECT TOP {1} row_number() OVER (ORDER BY c.ID DESC) rowNum, c.ID FROM {2} c join ProductionTask d on c.taskID = d.id where d.productID={4}) b WHERE a.ID = b.ID AND b.rowNum > {3} ORDER BY b.rowNum ASC",
                "CodeInfo", (page) * pageSize, "CodeInfo", (page - 1) * pageSize, productID);

            string errDesc = "";

            DataTable dt = SqlHelper.ExecuteTable(sql, ref errDesc);

            if (dt != null)
            {

                CodeInfo ci;
                List<CodeInfo> codes = new List<CodeInfo>();
                foreach (DataRow dr in dt.Rows)
                {
                    ci = new CodeInfo();
                    ci.code = dr["code"].ToString();
                    ci.codeState = Convert.ToInt32(dr["codeState"].ToString());
                    ci.ID = Convert.ToInt32(dr["ID"].ToString());
                    ci.inTime = Convert.ToDateTime(dr["inTime"].ToString());
                    ci.outsideCode = dr["outsideCode"].ToString();
                    ci.printTimes = Convert.ToInt32(dr["printTimes"].ToString());
                    ci.packageState = Convert.ToInt32(dr["packageState"].ToString());
                    ci.remarks = dr["remarks"].ToString();
                    ci.serialNumber = Convert.ToInt32(dr["serialNumber"].ToString());
                    ci.serialNumber2 = Convert.ToInt32(dr["serialNumber2"].ToString());
                    ci.sourceID = Convert.ToInt32(dr["sourceID"].ToString());
                    ci.stationNumber = Convert.ToInt32(dr["stationNumber"].ToString());
                    ci.subDeviceNumber = Convert.ToInt32(dr["subDeviceNumber"].ToString());
                    ci.taskID = Convert.ToInt32(dr["taskID"].ToString());

                    codes.Add(ci);
                }

                return codes;
            }
            else
            {
                msg = "查询历史数据出错：" + errDesc;
                return null;
            }
        }

        public static string RawQuerySingle(string sql)
        {
            //return SqlHelper.QuerySingle(sql, 112233);

            Dictionary<string, object> res = new Dictionary<string, object>();
            Dictionary<string, object> obj = new Dictionary<string, object>();
            bool bRes = false;
            string errDesc = string.Empty;

            DataTable dt = SqlHelper.ExecuteTable(sql, ref errDesc);

            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    obj.Add(dt.Columns[i].ColumnName, dt.Rows[0][i].ToString());
                }

                bRes = true;
            }
            else
            {
                errDesc = "无法查到记录";
            }


            res.Add("msg", errDesc);
            res.Add("res", bRes);
            res.Add("obj", JsonHelper.ToJson<Dictionary<string, object>>(obj));

            return JsonHelper.ToJson<Dictionary<string, object>>(res);
        }

        public static T QueryByID<T>(string id, ref string msg) where T : class, new()
        {
            string sql = EntityHelper.GetQuerySql<T>();
            sql += " where id = " + id;
            return QuerySingle<T>(sql, ref msg);
        }

        public static T QuerySingle<T>(string sql, ref string msg) where T : class, new()
        {
            string res = RawQuerySingle(sql);
            ResultEntity re = EntityHelper.GetResultEntityFromResult(res);
            Debug.WriteLine("QuerySingle: " + sql);
            if (re.res)
            {
                return EntityHelper.GetObjectFromResultEntity<T>(re);
            }
            else
            {
                msg = "查询任务出错：" + re.msg;
                return null;
            }

        }

        public static T QuerySingleArchieve<T>(string sql, ref string msg) where T : class, new()
        {
            string res = RawQueryArchieve(sql);
            ResultEntity re = EntityHelper.GetResultEntityFromResult(res);

            Debug.WriteLine("QuerySingleArchieve: " + sql);
            if (re.res)
            {
                List<T> li = EntityHelper.GetObjectsFromResultEntity<T>(re);

                if (li!=null && li.Count>0)
                {
                    return li[0];
                }
                else
                {
                    msg = "无该数据记录";
                    return null;
                }
            }
            else
            {
                msg = "查询任务出错：" + re.msg;
                return null;
            }

        }

        public static T QuerySingle<T>(ref string msg) where T : class, new()
        {
            string sql = EntityHelper.GetQuerySql<T>();
            string res = RawQuerySingle(sql);

            Debug.WriteLine("QuerySingle: " + sql);
            ResultEntity re = EntityHelper.GetResultEntityFromResult(res);

            if (re.res)
            {
                return EntityHelper.GetObjectFromResultEntity<T>(re);
            }
            else
            {
                msg = "查询任务出错：" + re.msg;
                return null;
            }

        }

        #endregion

        public static int GetNextObjectID(string tbName)
        {
            string sql = string.Format("select IDENT_CURRENT('{0}') + 1 as  nextID", tbName);
            string msg = string.Empty;

            int id = -1;
            
            try 
	        {
                object obj = RawGetScalar(sql, ref msg);

                id = System.DBNull.Value.Equals(obj) ? 1 : Convert.ToInt32(obj);

                return id;
	        }
            catch(Exception exp)
            {
                return -1;
            }
        }

        public static bool IfTableExist(string tbName)
        {
            //return SqlHelper.IfTableExist(tbName);

            string sql = string.Format("select COUNT(*) from dbo.sysobjects where id = object_id(N'{0}') and OBJECTPROPERTY(id, N'IsUserTable') = 1", tbName);
            string msg = string.Empty;

            object obj = SqlHelper.ExecuteScalar(sql, ref msg);

            if (string.IsNullOrEmpty(msg))
            {
                return obj != null && Convert.ToInt32(obj) > 0;
            }

            return false;
        }

        public static bool IfColumnExist(string tbName, string col)
        {
            //return SqlHelper.IfColumnExist(tbName, col);

            string sql = string.Format("select COL_LENGTH( '{0}','{1}')", tbName, col);
            string msg = string.Empty;

            object obj = SqlHelper.ExecuteScalar(sql, ref msg);

            if (string.IsNullOrEmpty(msg))
            {
                return obj != null && obj != DBNull.Value && Convert.ToInt32(obj) > 0;
            }

            return false;
        }

        public static bool IfColumnExistArchieve(string tbName, string col)
        {
            //return SqlHelper.IfColumnExist(tbName, col);

            string sql = string.Format("select COL_LENGTH( '{0}','{1}')", tbName, col);
            string msg = string.Empty;

            object obj = SqlHelper.ExecuteScalar("server=.\\SQLEXPRESS08;database=UniRlv_archive;uid=sa;pwd=connortao", sql, ref msg);

            if (string.IsNullOrEmpty(msg))
            {
                return obj != null && obj != DBNull.Value && Convert.ToInt32(obj) > 0;
            }

            return false;
        }

        public static bool AddColumn(string tbName, string col, string desc, ref string msg)
        {
            //return SqlHelper.AddColumn(tbName, col,  desc, ref msg);

            string sql = string.Format("alter table {0} add {1} {2}", tbName, col, desc);

            SqlHelper.ExecuteNonQuery(sql, ref msg);
            return true;


        }

        public static bool AddColumnArchieve(string tbName, string col, string desc, ref string msg)
        {
            //return SqlHelper.AddColumn(tbName, col,  desc, ref msg);

            string sql = string.Format("alter table {0} add {1} {2}", tbName, col, desc);

            SqlHelper.ExecuteNonQuery("server=.\\SQLEXPRESS08;database=UniRlv_archive;uid=sa;pwd=connortao", sql, ref msg);
            return true;


        }

        public static bool ExcuteBulkCopy(string tbDest, DataTable dtSrc, ref string msg)
        {
            //return SqlHelper.ExcuteBulkCopy(tbDest, dtSrc, ref msg);

            SqlBulkCopyColumnMapping map;
            List<SqlBulkCopyColumnMapping> maps = new List<SqlBulkCopyColumnMapping>();

            foreach (DataColumn item in dtSrc.Columns)
            {
                if (item.ColumnName.Equals("ID"))
                {
                    map = new SqlBulkCopyColumnMapping("id", "id");
                }
                else
                {
                    map = new SqlBulkCopyColumnMapping(item.ColumnName, item.ColumnName);
                }

                maps.Add(map);
            }

            try
            {
                SqlHelper.ExcuteBulkCopy(tbDest, dtSrc, maps.ToArray());
                return true;
            }
            catch (Exception exp)
            {
                msg = exp.Message;
                return false;
            }
        }

        public static bool UpdateRltData(IList<string> codes, string batch, string pzwh, ref string msg)
        {
            //return SqlHelper.ExcuteBulkCopy(tbDest, dtSrc, ref msg);
            msg = "";

            if (codes.Count == 0)
            {
                return true;
            }

            string sql = "";
            StringBuilder sb = new StringBuilder();
            int qty = 0;
            for (; qty < codes.Count; qty += 100)
            {
                sb.Clear();
                sb.Append("(");

                for (int i = qty; i < qty+100 && i<codes.Count; i++)
                {
                    sb.Append($"'{codes[i]}',");
                }

                sb.Append("'')");
                sql = $"update CodeRltInfo set codeState = 1 where batch='{batch}' and pzwh='{pzwh}' and code in {sb.ToString()}";

                ExecuteNonQuery(sql, ref msg);
            }

            return true;
        }

        /// <summary>
        /// 添加日志
        /// </summary>
        /// <param name="message"></param>
        /// <param name="remark"></param>
        /// <param name="logType"></param>
        public static void AddLog(string message, string remark, message_type logType)
        {
            LogInfo t = new LogInfo();

            t.msgTime = DateTime.Now;
            t.message = message;
            t.remarks = remark;
            t.type = (int)logType;
            t.stationNumber = 0;
            t.subDeviceNumber = 0;
            t.taskID = 0;
            t.lineID = 0;

            string msg = string.Empty;
            string sql = EntityHelper.GetInsertSql(t);

            ExecuteNonQuery(sql, ref msg);
        }

        /// <summary>
        /// 获取服务器配置
        /// </summary>
        /// <param name="sec">配置名称</param>
        /// <param name="msg">出错消息</param>
        /// <returns></returns>
        public static string GetSettingValue(string sec, ref string msg)
        {
            string sql = "select value from systemsetting where section='" + sec + "'";

            object obj = RawGetScalar(sql, ref msg);

            if (obj == null)
            {
                return string.Empty;
            }
            else
            {
                return obj.ToString();
            }
        }

        /// <summary>
        /// 设置服务器配置
        /// </summary>
        /// <param name="sec">配置名称</param>
        /// <param name="value">值</param>
        /// <param name="msg">出错消息</param>
        public static void SetSettingValue(string sec, string value, ref string msg)
        {
            string sql = string.Format("update systemsetting set value = '{0}' where section = '{1}'", value, sec);
            ExecuteNonQuery(sql, ref msg);
        }

        public static bool BKTasks(List<string> ids, ref string errDesc)
        {
            bool bRes = true;

            foreach (var item in ids)
            {
                bRes &= BkTask(Convert.ToInt32(item), ref errDesc);

                if (!bRes)
                {
                    return false;
                }
            }

            return true;
        }

        private static bool BkTask(int id, ref string msg)
        {
            msg = string.Empty;

            SqlParameter[] sp = new SqlParameter[4];

            sp[0] = new SqlParameter("@InStrPara", SqlDbType.VarChar, 1024);

            sp[1] = new SqlParameter("@wp", SqlDbType.Int);
            sp[1].Value = id;

            sp[2] = new SqlParameter("@lp", SqlDbType.Int);

            sp[3] = new SqlParameter("@Res", SqlDbType.Int);
            sp[3].Direction = ParameterDirection.Output;

            SqlHelper.ExecuteNonQueryProc("uni_backup_code_by_task", ref msg, sp);

            Debug.WriteLine(DateTime.Now.ToString("[HH:mm:ss.fff]") + "开始备份任务，id=" + id);
            if (!string.IsNullOrEmpty(msg))
            {
                Debug.WriteLine("备份任务错误：" + msg);

                return false;
            }
            Debug.WriteLine(DateTime.Now.ToString("[HH:mm:ss.fff]") + "结束备份任务");

            return true;
        }

        public static bool BkDB(int days, ref string msg)
        {
            SqlParameter[] sp = new SqlParameter[4];

            sp[0] = new SqlParameter("@InStrPara", SqlDbType.VarChar, 1024);

            sp[1] = new SqlParameter("@wp", SqlDbType.Int);
            sp[1].Value = days;

            sp[2] = new SqlParameter("@lp", SqlDbType.Int);

            sp[3] = new SqlParameter("@Res", SqlDbType.Int);
            sp[3].Direction = ParameterDirection.Output;

            SqlHelper.ExecuteNonQueryProc("uni_backup_code", ref msg, sp);

            Debug.WriteLine(DateTime.Now.ToString("[HH:mm:ss.fff]") + "开始备份数据库");
            if (!string.IsNullOrEmpty(msg))
            {
                Debug.WriteLine("备份数据库错误：" + msg);
                return false;
            }

            Debug.WriteLine(DateTime.Now.ToString("[HH:mm:ss.fff]") + "结束备份数据库");

            return true;
        }

        public static bool RestoreTasks(List<string> ids, ref string errDesc)
        {
            bool bRes = true;

            foreach (var item in ids)
            {
                bRes &= RsstoreTask(Convert.ToInt32(item), ref errDesc);

                if (!bRes)
                {
                    return false;
                }
            }

            return true;
        }

        private static bool RsstoreTask(int id, ref string msg)
        {
            msg = string.Empty;

            SqlParameter[] sp = new SqlParameter[4];

            sp[0] = new SqlParameter("@InStrPara", SqlDbType.VarChar, 1024);

            sp[1] = new SqlParameter("@wp", SqlDbType.Int);
            sp[1].Value = id;

            sp[2] = new SqlParameter("@lp", SqlDbType.Int);

            sp[3] = new SqlParameter("@Res", SqlDbType.Int);
            sp[3].Direction = ParameterDirection.Output;

            SqlHelper.ExecuteNonQueryProc("uni_restore_code_by_task", ref msg, sp);

            Debug.WriteLine(DateTime.Now.ToString("[HH:mm:ss.fff]") + "开始还原任务，id=" + id);
            if (!string.IsNullOrEmpty(msg))
            {
                Debug.WriteLine("备份任务错误：" + msg);

                return false;
            }
            Debug.WriteLine(DateTime.Now.ToString("[HH:mm:ss.fff]") + "结束还原任务");

            return true;
        }

    }
}
